﻿using Api.Common;
using Api.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Api.DAL
{
    /// <summary>
    /// 下载附件
    /// </summary>
    public partial class case_attach
    {
        private string databaseprefix = "eazy_"; //数据库表名前缀
        public case_attach()
        { }
        #region Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "case_attach");
            strSql.Append(" where id=@id ");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        public int Add(List<Models.case_attach> ls)
        {
            int i = 0;
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        if (ls != null)
                        {
                            StringBuilder strSql4 = new StringBuilder();
                            foreach (Models.case_attach modelt in ls)
                            {
                                strSql4.Append("insert into " + databaseprefix + "case_attach(");
                                strSql4.Append("instance_id,article_id,file_name,file_path,file_size,file_ext,down_num,point,add_time,user_name,step_id)");
                                strSql4.Append(" values (");
                                strSql4.Append("@instance_id,@article_id,@file_name,@file_path,@file_size,@file_ext,@down_num,@point,@add_time,@user_name,@step_id)");
                                strSql4.Append(";select @@IDENTITY");
                                SqlParameter[] parameters = {
                                            new SqlParameter("@instance_id", SqlDbType.Int,4),
                                            new SqlParameter("@article_id", SqlDbType.Int,4),
                                            new SqlParameter("@file_name", SqlDbType.NVarChar,255),
                                            new SqlParameter("@file_path", SqlDbType.NVarChar,255),
                                            new SqlParameter("@file_size", SqlDbType.Int,4),
                                            new SqlParameter("@file_ext", SqlDbType.NVarChar,20),
                                            new SqlParameter("@down_num", SqlDbType.Int,4),
                                            new SqlParameter("@point", SqlDbType.Int,4),
                                            new SqlParameter("@add_time", SqlDbType.DateTime),
                                            new SqlParameter("@user_name", SqlDbType.NVarChar,20),
                                            new SqlParameter("@step_id", SqlDbType.Int,4)};
                                parameters[0].Value = modelt.instance_id;
                                parameters[1].Value = modelt.article_id;
                                parameters[2].Value = modelt.file_name;
                                parameters[3].Value = modelt.file_path;
                                parameters[4].Value = modelt.file_size;
                                parameters[5].Value = modelt.file_ext;
                                parameters[6].Value = modelt.down_num;
                                parameters[7].Value = modelt.point;
                                parameters[8].Value = modelt.add_time;
                                parameters[9].Value = modelt.user_name;
                                parameters[10].Value = modelt.step_id;
                                DbHelperSQL.GetSingle(conn, trans, strSql4.ToString(), parameters); //带事务
                                i++;
                            }
                        }
                        trans.Commit();
                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        return i;
                    }
                }
            }
            return i;
        }

        /// <summary>
        /// 检查用户是否下载过该附件
        /// </summary>
        public bool ExistsLog(int attach_id, int user_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "user_attach_log");
            strSql.Append(" where attach_id=@attach_id and user_id=@user_id");
            SqlParameter[] parameters = {
                    new SqlParameter("@attach_id", SqlDbType.Int,4),
                    new SqlParameter("@user_id", SqlDbType.Int,4)};
            parameters[0].Value = attach_id;
            parameters[1].Value = user_id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取单个附件下载次数
        /// </summary>
        public int GetDownNum(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 down_num from " + databaseprefix + "case_attach");
            strSql.Append(" where id=" + id);
            string str = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(str))
            {
                return 0;
            }
            return Convert.ToInt32(str);
        }

        /// <summary>
        /// 获取总下载次数
        /// </summary>
        public int GetCountNum(int article_id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select sum(down_num) from " + databaseprefix + "case_attach");
            strSql.Append(" where article_id=" + article_id);
            string str = Convert.ToString(DbHelperSQL.GetSingle(strSql.ToString()));
            if (string.IsNullOrEmpty(str))
            {
                return 0;
            }
            return Convert.ToInt32(str);
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public void UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "case_attach set " + strValue);
            strSql.Append(" where id=" + id);
            DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 查找不存在的文件并删除已删除的附件及数据
        /// </summary>
        public void DeleteList(SqlConnection conn, SqlTransaction trans, List<Models.case_attach> models, int article_id)
        {
            StringBuilder idList = new StringBuilder();
            if (models != null)
            {
                foreach (Models.case_attach modelt in models)
                {
                    if (modelt.id > 0)
                    {
                        idList.Append(modelt.id + ",");
                    }
                }
            }
            string id_list = Utils.DelLastChar(idList.ToString(), ",");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,file_path from " + databaseprefix + "case_attach where article_id=" + article_id);
            if (!string.IsNullOrEmpty(id_list))
            {
                strSql.Append(" and id not in(" + id_list + ")");
            }
            DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                int rows = DbHelperSQL.ExecuteSql(conn, trans, "delete from " + databaseprefix + "case_attach where id=" + dr["id"].ToString()); //删除数据库
                if (rows > 0)
                {
                    Utils.DeleteFile(dr["file_path"].ToString()); //删除文件
                }
            }
        }

        /// <summary>
        /// 查找不存在的文件并删除已删除的附件及数据
        /// </summary>
        public void DeleteList_I(SqlConnection conn, SqlTransaction trans, List<Models.case_attach> models, int instance_id)
        {
            StringBuilder idList = new StringBuilder();
            if (models != null)
            {
                foreach (Models.case_attach modelt in models)
                {
                    if (modelt.id > 0)
                    {
                        idList.Append(modelt.id + ",");
                    }
                }
            }
            string id_list = Utils.DelLastChar(idList.ToString(), ",");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,file_path from " + databaseprefix + "case_attach where instance_id=" + instance_id);
            if (!string.IsNullOrEmpty(id_list))
            {
                strSql.Append(" and id not in(" + id_list + ")");
            }
            DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                int rows = DbHelperSQL.ExecuteSql(conn, trans, "delete from " + databaseprefix + "case_attach where id=" + dr["id"].ToString()); //删除数据库
                if (rows > 0)
                {
                    Utils.DeleteFile(dr["file_path"].ToString()); //删除文件
                }
            }
        }

        /// <summary>
        /// 删除附件文件
        /// </summary>
        public void DeleteFile(List<Models.case_attach> models)
        {
            if (models != null)
            {
                foreach (Models.case_attach modelt in models)
                {
                    Utils.DeleteFile(modelt.file_path);
                }
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Models.case_attach GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 id,instance_id,article_id,file_name,file_path,file_size,file_ext,down_num,point,add_time,user_name from " + databaseprefix + "case_attach ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = id;

            Models.case_attach model = new Models.case_attach();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["article_id"].ToString() != "")
                {
                    model.article_id = int.Parse(ds.Tables[0].Rows[0]["article_id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["instance_id"].ToString() != "")
                {
                    model.instance_id = int.Parse(ds.Tables[0].Rows[0]["instance_id"].ToString());
                }
                model.file_name = ds.Tables[0].Rows[0]["file_name"].ToString();
                model.file_path = ds.Tables[0].Rows[0]["file_path"].ToString();
                if (ds.Tables[0].Rows[0]["file_size"].ToString() != "")
                {
                    model.file_size = int.Parse(ds.Tables[0].Rows[0]["file_size"].ToString());
                }
                model.file_ext = ds.Tables[0].Rows[0]["file_ext"].ToString();
                if (ds.Tables[0].Rows[0]["down_num"].ToString() != "")
                {
                    model.down_num = int.Parse(ds.Tables[0].Rows[0]["down_num"].ToString());
                }
                if (ds.Tables[0].Rows[0]["point"].ToString() != "")
                {
                    model.point = int.Parse(ds.Tables[0].Rows[0]["point"].ToString());
                }
                if (ds.Tables[0].Rows[0]["add_time"].ToString() != "")
                {
                    model.add_time = DateTime.Parse(ds.Tables[0].Rows[0]["add_time"].ToString());
                }
                model.user_name = ds.Tables[0].Rows[0]["user_name"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<Models.case_attach> GetList_I(string instance_id)
        {
            List<Models.case_attach> modelList = new List<Models.case_attach>();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,article_id,instance_id,file_name,file_path,file_size,file_ext,down_num,point,add_time,user_name,step_id ");
            strSql.Append(" FROM " + databaseprefix + "case_attach ");
            strSql.Append(" where instance_id=" + instance_id);
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];

            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Models.case_attach model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new Models.case_attach();
                    if (dt.Rows[n]["id"] != null && dt.Rows[n]["id"].ToString() != "")
                    {
                        model.id = int.Parse(dt.Rows[n]["id"].ToString());
                    }
                    if (dt.Rows[n]["instance_id"] != null && dt.Rows[n]["instance_id"].ToString() != "")
                    {
                        model.instance_id = int.Parse(dt.Rows[n]["instance_id"].ToString());
                    }
                    if (dt.Rows[n]["article_id"] != null && dt.Rows[n]["article_id"].ToString() != "")
                    {
                        model.article_id = int.Parse(dt.Rows[n]["article_id"].ToString());
                    }
                    if (dt.Rows[n]["step_id"] != null && dt.Rows[n]["step_id"].ToString() != "")
                    {
                        model.step_id = int.Parse(dt.Rows[n]["step_id"].ToString());
                    }
                    if (dt.Rows[n]["file_name"] != null)
                    {
                        model.file_name = dt.Rows[n]["file_name"].ToString();
                    }
                    if (dt.Rows[n]["file_path"] != null)
                    {
                        model.file_path = dt.Rows[n]["file_path"].ToString();
                    }
                    if (dt.Rows[n]["file_size"] != null && dt.Rows[n]["file_size"].ToString() != "")
                    {
                        model.file_size = int.Parse(dt.Rows[n]["file_size"].ToString());
                    }
                    if (dt.Rows[n]["file_ext"] != null)
                    {
                        model.file_ext = dt.Rows[n]["file_ext"].ToString();
                    }
                    if (dt.Rows[n]["down_num"] != null && dt.Rows[n]["down_num"].ToString() != "")
                    {
                        model.down_num = int.Parse(dt.Rows[n]["down_num"].ToString());
                    }
                    if (dt.Rows[n]["point"] != null && dt.Rows[n]["point"].ToString() != "")
                    {
                        model.point = int.Parse(dt.Rows[n]["point"].ToString());
                    }
                    if (dt.Rows[n]["add_time"] != null && dt.Rows[n]["add_time"].ToString() != "")
                    {
                        model.add_time = DateTime.Parse(dt.Rows[n]["add_time"].ToString());
                    }
                    if (dt.Rows[n]["user_name"] != null)
                    {
                        model.user_name = dt.Rows[n]["user_name"].ToString();
                    }
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<Models.case_attach> GetList(int article_id)
        {
            List<Models.case_attach> modelList = new List<Models.case_attach>();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,instance_id,article_id,file_name,file_path,file_size,file_ext,down_num,point,add_time,user_name,step_id ");
            strSql.Append(" FROM " + databaseprefix + "case_attach ");
            strSql.Append(" where article_id=" + article_id);
            DataTable dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];

            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                Models.case_attach model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new Models.case_attach();
                    if (dt.Rows[n]["id"] != null && dt.Rows[n]["id"].ToString() != "")
                    {
                        model.id = int.Parse(dt.Rows[n]["id"].ToString());
                    }
                    if (dt.Rows[n]["instance_id"] != null && dt.Rows[n]["instance_id"].ToString() != "")
                    {
                        model.instance_id = int.Parse(dt.Rows[n]["instance_id"].ToString());
                    }
                    if (dt.Rows[n]["article_id"] != null && dt.Rows[n]["article_id"].ToString() != "")
                    {
                        model.article_id = int.Parse(dt.Rows[n]["article_id"].ToString());
                    }
                    if (dt.Rows[n]["step_id"] != null && dt.Rows[n]["step_id"].ToString() != "")
                    {
                        model.step_id = int.Parse(dt.Rows[n]["step_id"].ToString());
                    }
                    if (dt.Rows[n]["file_name"] != null)
                    {
                        model.file_name = dt.Rows[n]["file_name"].ToString();
                    }
                    if (dt.Rows[n]["file_path"] != null)
                    {
                        model.file_path = dt.Rows[n]["file_path"].ToString();
                    }
                    if (dt.Rows[n]["file_size"] != null && dt.Rows[n]["file_size"].ToString() != "")
                    {
                        model.file_size = int.Parse(dt.Rows[n]["file_size"].ToString());
                    }
                    if (dt.Rows[n]["file_ext"] != null)
                    {
                        model.file_ext = dt.Rows[n]["file_ext"].ToString();
                    }
                    if (dt.Rows[n]["down_num"] != null && dt.Rows[n]["down_num"].ToString() != "")
                    {
                        model.down_num = int.Parse(dt.Rows[n]["down_num"].ToString());
                    }
                    if (dt.Rows[n]["point"] != null && dt.Rows[n]["point"].ToString() != "")
                    {
                        model.point = int.Parse(dt.Rows[n]["point"].ToString());
                    }
                    if (dt.Rows[n]["add_time"] != null && dt.Rows[n]["add_time"].ToString() != "")
                    {
                        model.add_time = DateTime.Parse(dt.Rows[n]["add_time"].ToString());
                    }
                    if (dt.Rows[n]["user_name"] != null)
                    {
                        model.user_name = dt.Rows[n]["user_name"].ToString();
                    }
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        /// <summary>
        /// 得到数据列表
        /// </summary>
        public DataTable GetList_I(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * from " + databaseprefix + "case_attach ");
            strSql.Append(" where instance_id=" + id);
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        #endregion
    }
}
